"""
Excel file parser using openpyxl
"""
import time
from pathlib import Path
from typing import Union, List, Dict, Any
import logging

try:
    from openpyxl import load_workbook
    from openpyxl.utils.exceptions import InvalidFileException
except ImportError:
    load_workbook = None

from .base import FileParser, ParseResult, FileType

logger = logging.getLogger(__name__)


class ExcelParser(FileParser):
    """
    Parser for Excel files using openpyxl
    """
    
    def __init__(self, max_file_size: int = 50 * 1024 * 1024):
        super().__init__(max_file_size)
        
        if load_workbook is None:
            raise ImportError("openpyxl is required for Excel parsing. Install with: pip install openpyxl")
    
    def supports_file_type(self, file_type: FileType) -> bool:
        """Check if parser supports Excel files"""
        return file_type == FileType.EXCEL
    
    def parse(self, file_path: Union[str, Path]) -> ParseResult:
        """
        Parse Excel file and extract text content from all worksheets
        
        Args:
            file_path: Path to the Excel file
            
        Returns:
            ParseResult with extracted text and metadata
        """
        start_time = time.time()
        file_path = Path(file_path)
        
        # Validate file
        if not self.validate_file(file_path):
            return self.create_error_result("File validation failed", file_path)
        
        try:
            # Load workbook
            workbook = load_workbook(str(file_path), read_only=True, data_only=True)
            
            content_parts = []
            metadata = {
                "worksheet_count": len(workbook.worksheets),
                "worksheet_names": workbook.sheetnames,
                "active_sheet": workbook.active.title if workbook.active else None,
            }
            
            total_rows = 0
            total_cols = 0
            
            # Process each worksheet
            for sheet_idx, worksheet in enumerate(workbook.worksheets):
                sheet_name = worksheet.title
                content_parts.append(f"\n=== Worksheet: {sheet_name} ===\n")
                
                # Get worksheet dimensions
                max_row = worksheet.max_row
                max_col = worksheet.max_column
                
                if max_row == 1 and max_col == 1:
                    # Check if the single cell is empty
                    cell_value = worksheet.cell(1, 1).value
                    if cell_value is None:
                        content_parts.append("(Empty worksheet)\n")
                        continue
                
                total_rows += max_row
                total_cols = max(total_cols, max_col)
                
                # Extract data row by row
                sheet_data = []
                for row in worksheet.iter_rows(min_row=1, max_row=max_row, min_col=1, max_col=max_col, values_only=True):
                    # Convert row values to strings, handle None values
                    row_data = []
                    for cell_value in row:
                        if cell_value is None:
                            row_data.append("")
                        else:
                            # Handle different data types
                            if isinstance(cell_value, (int, float)):
                                row_data.append(str(cell_value))
                            else:
                                row_data.append(str(cell_value).strip())
                    
                    # Only add rows with at least one non-empty cell
                    if any(cell.strip() for cell in row_data):
                        sheet_data.append(row_data)
                
                if sheet_data:
                    # Format as table-like text
                    if len(sheet_data) > 1:
                        # Treat first row as headers if it looks like headers
                        headers = sheet_data[0]
                        data_rows = sheet_data[1:]
                        
                        # Add headers
                        content_parts.append(" | ".join(headers))
                        content_parts.append("\n" + "-" * len(" | ".join(headers)) + "\n")
                        
                        # Add data rows
                        for row in data_rows:
                            content_parts.append(" | ".join(row) + "\n")
                    else:
                        # Single row
                        content_parts.append(" | ".join(sheet_data[0]) + "\n")
                else:
                    content_parts.append("(No data found)\n")
                
                content_parts.append("\n")
            
            workbook.close()
            
            # Update metadata
            metadata.update({
                "total_rows": total_rows,
                "max_columns": total_cols,
            })
            
            # Combine all content
            full_content = "".join(content_parts)
            
            if not full_content.strip():
                return self.create_error_result("No data found in Excel file", file_path)
            
            processing_time = time.time() - start_time
            
            return self.create_success_result(
                content=full_content,
                file_path=file_path,
                file_type=FileType.EXCEL,
                processing_time=processing_time,
                additional_metadata=metadata
            )
            
        except InvalidFileException as e:
            error_msg = f"Invalid Excel file format: {str(e)}"
            logger.error(error_msg)
            return self.create_error_result(error_msg, file_path)
        
        except Exception as e:
            error_msg = f"Error parsing Excel file: {str(e)}"
            logger.error(error_msg)
            return self.create_error_result(error_msg, file_path)
    
    def extract_worksheet(self, file_path: Union[str, Path], worksheet_name: str) -> ParseResult:
        """
        Extract data from a specific worksheet
        
        Args:
            file_path: Path to the Excel file
            worksheet_name: Name of the worksheet to extract
            
        Returns:
            ParseResult with extracted worksheet data
        """
        start_time = time.time()
        file_path = Path(file_path)
        
        if not self.validate_file(file_path):
            return self.create_error_result("File validation failed", file_path)
        
        try:
            workbook = load_workbook(str(file_path), read_only=True, data_only=True)
            
            if worksheet_name not in workbook.sheetnames:
                available_sheets = ", ".join(workbook.sheetnames)
                error_msg = f"Worksheet '{worksheet_name}' not found. Available sheets: {available_sheets}"
                return self.create_error_result(error_msg, file_path)
            
            worksheet = workbook[worksheet_name]
            
            # Extract worksheet data
            content_parts = [f"=== Worksheet: {worksheet_name} ===\n\n"]
            
            max_row = worksheet.max_row
            max_col = worksheet.max_column
            
            sheet_data = []
            for row in worksheet.iter_rows(min_row=1, max_row=max_row, min_col=1, max_col=max_col, values_only=True):
                row_data = []
                for cell_value in row:
                    if cell_value is None:
                        row_data.append("")
                    else:
                        row_data.append(str(cell_value).strip())
                
                if any(cell.strip() for cell in row_data):
                    sheet_data.append(row_data)
            
            if sheet_data:
                # Format as table
                for row in sheet_data:
                    content_parts.append(" | ".join(row) + "\n")
            else:
                content_parts.append("(No data found)")
            
            workbook.close()
            
            content = "".join(content_parts)
            processing_time = time.time() - start_time
            
            metadata = {
                "worksheet_name": worksheet_name,
                "rows": len(sheet_data),
                "columns": max_col,
            }
            
            return self.create_success_result(
                content=content,
                file_path=file_path,
                file_type=FileType.EXCEL,
                processing_time=processing_time,
                additional_metadata=metadata
            )
            
        except Exception as e:
            error_msg = f"Error extracting worksheet '{worksheet_name}': {str(e)}"
            logger.error(error_msg)
            return self.create_error_result(error_msg, file_path)
    
    def get_worksheet_data(self, file_path: Union[str, Path]) -> Dict[str, List[List[str]]]:
        """
        Get structured data from all worksheets
        
        Args:
            file_path: Path to the Excel file
            
        Returns:
            Dictionary with worksheet names as keys and data as values
        """
        file_path = Path(file_path)
        
        if not self.validate_file(file_path):
            return {}
        
        try:
            workbook = load_workbook(str(file_path), read_only=True, data_only=True)
            worksheets_data = {}
            
            for worksheet in workbook.worksheets:
                sheet_name = worksheet.title
                sheet_data = []
                
                max_row = worksheet.max_row
                max_col = worksheet.max_column
                
                for row in worksheet.iter_rows(min_row=1, max_row=max_row, min_col=1, max_col=max_col, values_only=True):
                    row_data = []
                    for cell_value in row:
                        if cell_value is None:
                            row_data.append("")
                        else:
                            row_data.append(str(cell_value).strip())
                    
                    if any(cell.strip() for cell in row_data):
                        sheet_data.append(row_data)
                
                worksheets_data[sheet_name] = sheet_data
            
            workbook.close()
            return worksheets_data
            
        except Exception as e:
            logger.error(f"Error getting worksheet data: {e}")
            return {}
    
    def get_workbook_info(self, file_path: Union[str, Path]) -> dict:
        """
        Get Excel workbook metadata without extracting content
        
        Args:
            file_path: Path to the Excel file
            
        Returns:
            Dictionary with workbook metadata
        """
        file_path = Path(file_path)
        
        try:
            workbook = load_workbook(str(file_path), read_only=True)
            
            info = {
                "worksheet_count": len(workbook.worksheets),
                "worksheet_names": workbook.sheetnames,
                "active_sheet": workbook.active.title if workbook.active else None,
            }
            
            # Get total rows and columns across all sheets
            total_rows = 0
            max_cols = 0
            
            for worksheet in workbook.worksheets:
                total_rows += worksheet.max_row
                max_cols = max(max_cols, worksheet.max_column)
            
            info.update({
                "total_rows": total_rows,
                "max_columns": max_cols,
            })
            
            # Try to get document properties
            try:
                props = workbook.properties
                if props:
                    info.update({
                        "title": props.title or "",
                        "subject": props.subject or "",
                        "creator": props.creator or "",
                        "created": str(props.created) if props.created else "",
                        "modified": str(props.modified) if props.modified else "",
                    })
            except Exception:
                pass
            
            workbook.close()
            return info
            
        except Exception as e:
            logger.error(f"Error getting workbook info: {e}")
            return {"error": str(e)}